Imports System.Web

Public Class Stat
    Inherits Connessione

    Private _selezionati As DataTable
    Private _campiVisibili As DataTable
    Private _campiOrdinamento As DataTable
    Private _campiSomma As DataTable
    Private _campiConta As DataTable
    Private _campiRaggruppa As DataTable

    Private _risultato As DataTable

    Private _filtro As DataTable

    Private _oper As String
    Private _arQuery As New SortedList

    Private _id As String
    Private _nome As String
    Private _descrizione As String

    Private _sqlPronta As String
    Private _pronta As Boolean
    Private _tipo As Int16

    Private _storico As String

    Public Property storico() As String
        Get
            Return _storico
        End Get
        Set(ByVal Value As String)
            _storico = Value
        End Set
    End Property
    Public Property tipo() As Int16
        Get
            Return _tipo
        End Get
        Set(ByVal Value As Int16)
            _tipo = Value
        End Set
    End Property

    Public Property pronta() As Boolean
        Get
            Return _pronta
        End Get
        Set(ByVal Value As Boolean)
            _pronta = Value
        End Set
    End Property
    Public Property ris() As DataTable
        Get
            Return _risultato
        End Get
        Set(ByVal Value As DataTable)
            _risultato = Value
        End Set
    End Property

    Public Property descrizione() As String
        Get
            Return _descrizione
        End Get
        Set(ByVal Value As String)
            _descrizione = Value
        End Set
    End Property

    Public Property nome() As String
        Get
            Return _nome
        End Get
        Set(ByVal Value As String)
            _nome = Value
        End Set
    End Property

    Public Property id() As String
        Get
            Return _id
        End Get
        Set(ByVal Value As String)
            _id = Value
        End Set
    End Property


    Public Property oper() As String
        Get
            Return _oper
        End Get
        Set(ByVal Value As String)
            _oper = Value
        End Set
    End Property

    Public Property filtro() As DataTable
        Get
            Return _filtro
        End Get
        Set(ByVal Value As DataTable)
            _filtro = Value
        End Set
    End Property


    Public Property campiVisibili() As DataTable
        Get
            Return _campiVisibili
        End Get
        Set(ByVal Value As DataTable)
            _campiVisibili = Value
        End Set
    End Property

    Public Property campiOrdinamento() As DataTable
        Get
            Return _campiOrdinamento
        End Get
        Set(ByVal Value As DataTable)
            _campiOrdinamento = Value
        End Set
    End Property

    Public Property campisomma() As DataTable
        Get
            Return _campiSomma
        End Get
        Set(ByVal Value As DataTable)
            _campiSomma = Value
        End Set
    End Property

    Public Property campiConta() As DataTable
        Get
            Return _campiConta
        End Get
        Set(ByVal Value As DataTable)
            _campiConta = Value
        End Set
    End Property

    Public Property campiRaggruppa() As DataTable
        Get
            Return _campiRaggruppa
        End Get
        Set(ByVal Value As DataTable)
            _campiRaggruppa = Value
        End Set
    End Property

    Public Property selezionati() As DataTable
        Get
            Return _selezionati
        End Get
        Set(ByVal Value As DataTable)
            _selezionati = Value
        End Set
    End Property


    Public Function elencoCampi(ByVal tabella As String) As DataTable
        Dim sql As String = "select ltrim(str(id)) as id, etitabella, tabella, campo, etichetta, tipo, vista from campistampe where nascondi = 0 "
        If tabella <> "" Then
            sql += " and tabella ='" & tabella & "'"
        End If
        sql += " and (tipoTab = 0 or tipotab=" & _tipo & ") "
        sql += " order by etitabella, etichetta"
        Return getDataTable(sql)
    End Function

    Public Function elencoTabelle() As DataTable
        Return getDataTable("select '' as tabella, '' as etitabella union select distinct tabella, etitabella from campistampe where nascondi=0  and (tipoTab = 0 or tipotab=" & _tipo & ") order by etitabella")
    End Function

    Public Sub creaFiltro()
        _filtro = New DataTable
        Dim fIdColumn As DataColumn = New DataColumn
        fIdColumn.DataType = System.Type.GetType("System.String")
        fIdColumn.ColumnName = "id"
        _filtro.Columns.Add(fIdColumn)
        Dim fetiTabColumn As DataColumn = New DataColumn
        fetiTabColumn.DataType = System.Type.GetType("System.String")
        fetiTabColumn.ColumnName = "etitabella"
        _filtro.Columns.Add(fetiTabColumn)
        Dim fTabColumn As DataColumn = New DataColumn
        fTabColumn.DataType = System.Type.GetType("System.String")
        fTabColumn.ColumnName = "tabella"
        _filtro.Columns.Add(fTabColumn)
        Dim fCampoColumn As DataColumn = New DataColumn
        fCampoColumn.DataType = System.Type.GetType("System.String")
        fCampoColumn.ColumnName = "campo"
        _filtro.Columns.Add(fCampoColumn)
        Dim fOpColumn As DataColumn = New DataColumn
        fOpColumn.DataType = System.Type.GetType("System.Int16")
        fOpColumn.DefaultValue = 0
        fOpColumn.ColumnName = "op"
        _filtro.Columns.Add(fOpColumn)
        Dim fOpTabColumn As DataColumn = New DataColumn
        fOpTabColumn.DataType = System.Type.GetType("System.String")
        fOpTabColumn.ColumnName = "opTab"
        _filtro.Columns.Add(fOpTabColumn)
        Dim fValoreColumn As DataColumn = New DataColumn
        fValoreColumn.DataType = System.Type.GetType("System.String")
        fValoreColumn.ColumnName = "valore"
        _filtro.Columns.Add(fValoreColumn)
        Dim fValoreTabColumn As DataColumn = New DataColumn
        fValoreTabColumn.DataType = System.Type.GetType("System.String")
        fValoreTabColumn.ColumnName = "valoreTab"
        _filtro.Columns.Add(fValoreTabColumn)
        Dim fValore2Column As DataColumn = New DataColumn
        fValore2Column.DataType = System.Type.GetType("System.String")
        fValore2Column.ColumnName = "valore2"
        _filtro.Columns.Add(fValore2Column)
        Dim fValore2TabColumn As DataColumn = New DataColumn
        fValore2TabColumn.DataType = System.Type.GetType("System.String")
        fValore2TabColumn.ColumnName = "valore2Tab"
        _filtro.Columns.Add(fValore2TabColumn)
        Dim fTipoColumn As DataColumn = New DataColumn
        fTipoColumn.DataType = System.Type.GetType("System.Int16")
        fTipoColumn.ColumnName = "tipo"
        _filtro.Columns.Add(fTipoColumn)
        Dim fVista As DataColumn = New DataColumn
        fVista.DataType = System.Type.GetType("System.String")
        fVista.ColumnName = "vista"
        _filtro.Columns.Add(fVista)

    End Sub

    Public Sub creaRis()
        _risultato = New DataTable
        Dim fIdColumn As DataColumn = New DataColumn
        fIdColumn.DataType = System.Type.GetType("System.String")
        fIdColumn.ColumnName = "id"
        _risultato.Columns.Add(fIdColumn)
        Dim fIdAnaColumn As DataColumn = New DataColumn
        fIdAnaColumn.DataType = System.Type.GetType("System.String")
        fIdAnaColumn.ColumnName = "idAna"
        _risultato.Columns.Add(fIdAnaColumn)
        Dim fIdProfColumn As DataColumn = New DataColumn
        fIdProfColumn.DataType = System.Type.GetType("System.String")
        fIdProfColumn.ColumnName = "idProf"
        _risultato.Columns.Add(fIdProfColumn)
    End Sub

    Public Sub creaSel()
        _selezionati = New DataTable

        Dim fIdColumn As DataColumn = New DataColumn
        fIdColumn.DataType = System.Type.GetType("System.String")
        fIdColumn.ColumnName = "id"
        _selezionati.Columns.Add(fIdColumn)
        Dim fetiTabColumn As DataColumn = New DataColumn
        fetiTabColumn.DataType = System.Type.GetType("System.String")
        fetiTabColumn.ColumnName = "etitabella"
        _selezionati.Columns.Add(fetiTabColumn)
        Dim fTabColumn As DataColumn = New DataColumn
        fTabColumn.DataType = System.Type.GetType("System.String")
        fTabColumn.ColumnName = "tabella"
        _selezionati.Columns.Add(fTabColumn)
        Dim fCampoColumn As DataColumn = New DataColumn
        fCampoColumn.DataType = System.Type.GetType("System.String")
        fCampoColumn.ColumnName = "campo"
        _selezionati.Columns.Add(fCampoColumn)
        Dim fEtiColumn As DataColumn = New DataColumn
        fEtiColumn.DataType = System.Type.GetType("System.String")
        fEtiColumn.ColumnName = "etichetta"
        _selezionati.Columns.Add(fEtiColumn)
        Dim fTipoColumn As DataColumn = New DataColumn
        fTipoColumn.DataType = System.Type.GetType("System.Int16")
        fTipoColumn.ColumnName = "tipo"
        _selezionati.Columns.Add(fTipoColumn)

        setKey()
    End Sub

    Public Sub setKey()
        Dim PrimaryKeyColumns(0) As DataColumn
        PrimaryKeyColumns(0) = _selezionati.Columns("id")
        _selezionati.PrimaryKey = PrimaryKeyColumns

    End Sub


    Public Sub addSel(ByVal id As String, ByVal etitabella As String, ByVal tabella As String, ByVal campo As String, ByVal etichetta As String, ByVal tipo As String)
        Dim dr As DataRow = _selezionati.NewRow
        dr("id") = id
        dr("tabella") = tabella
        dr("etitabella") = etitabella
        dr("campo") = campo
        dr("etichetta") = etichetta
        dr("tipo") = tipo
        _selezionati.Rows.Add(dr)

    End Sub

    Public Sub addNewFiltro(ByVal id As String, ByVal etitabella As String, ByVal tabella As String, ByVal campo As String, ByVal tipo As Int16, ByVal vista As String)
        Dim dr As DataRow = _filtro.NewRow
        dr("id") = id
        dr("etitabella") = etitabella
        dr("tabella") = tabella
        dr("campo") = campo
        dr("op") = 0
        dr("tipo") = tipo
        dr("vista") = vista
        _filtro.Rows.Add(dr)

    End Sub

    Public Sub updateFiltro(ByVal num As Int16, ByVal valore As String, ByVal valore2 As String, ByVal op As Int16, ByVal opTab As String, ByVal valoreTab As String, ByVal valore2Tab As String)
        _filtro.Rows(num)("valore") = valore
        _filtro.Rows(num)("valore2") = valore2
        _filtro.Rows(num)("op") = op
        _filtro.Rows(num)("valoreTab") = valoreTab
        _filtro.Rows(num)("valore2Tab") = valore2Tab
        _filtro.Rows(num)("opTab") = opTab
    End Sub

    Public Sub levaSel(ByVal id As String)
        Dim dr As DataRow
        If _selezionati.Rows.Contains(id) Then
            dr = _selezionati.Rows.Find(id)
            _selezionati.Rows.Remove(dr)
        End If
    End Sub


    Public Sub levaFiltro(ByVal num As Int16)
        _filtro.Rows.RemoveAt(num)
    End Sub

    Private Sub creaQuery(ByVal id As Int16)
        Dim dt As DataTable = getDataTable("SELECT sqlWhere, dipendeDa FROM sqlJoin where id =" & id)

        If dt.Rows.Count > 0 Then
            Dim dr As DataRow = dt.Rows(0)
            If Not dr("dipendeDa") Is System.DBNull.Value Then
                creaQuery(dr("dipendeDa"))
            End If

            If Not _arQuery.Contains(id) Then
                _arQuery.Add(id, dr("sqlWhere"))
            End If
        End If
    End Sub

    Private Function datiSql(ByVal id As String) As String
        Dim sql As String = "SELECT sqlSelect, sqljoin FROM CampiStampe WHERE id = " & id
        Dim dr As DataRow = getDataTable(sql).Rows(0)
        If Not dr("sqlJoin") Is System.DBNull.Value Then
            creaQuery(dr("sqljoin"))
        End If
        If Not dr("sqlSelect") Is System.DBNull.Value Then
            Return dr("sqlSelect")
        Else
            Return ""
        End If
    End Function

    Public Function risPronta() As DataTable
        Return getDataTable(_sqlPronta)
    End Function


    Public Function risultati(ByVal sId As String) As DataTable
        Dim sqlSelect As String = "select distinct anagrafica.id, "
        Dim sqlGroup As String = ""
        Dim sqlOrder As String = ""
        Dim sqlWhere As String = ""
        Dim sqlFrom As String
        Dim sql As String
        Dim x As Int16
        Dim dr As DataRow
        Dim s As String

        _arQuery.Clear()

        'elenco campi select
        Dim pos As Int16
        Dim idCampo As String
        For x = 0 To _campiVisibili.Rows.Count - 1
            dr = _campiVisibili.Rows(x)
            pos = InStr(dr("id"), "_")
            If pos > 0 Then
                idCampo = Left(dr("id"), pos - 1)
            Else
                idCampo = dr("id")
            End If
            s = datiSql(idCampo)
            If s = "" Then
                s = dr("tabella") & "." & dr("campo")
            End If

            If dr("tipo") = "3" Then
                'data
                s = " convert(char(10)," & s & ",103) "
            End If
            s += " as [" & dr("etichetta") & "]"
            sqlSelect += s & ", "

        Next


        sqlSelect = Left(sqlSelect, Len(sqlSelect) - 2)

        'elenco campi da ordinare
        If _campiOrdinamento.Rows.Count > 0 Then
            sqlOrder = " order by "
        End If
        For x = 0 To _campiOrdinamento.Rows.Count - 1
            dr = _campiOrdinamento.Rows(x)
            s = datiSql(dr("id"))
            If s = "" Then
                s = dr("tabella") & "." & dr("campo")
            End If
            sqlOrder += s & ", "
        Next
        If sqlOrder <> "" Then
            sqlOrder = Left(sqlOrder, Len(sqlOrder) - 2)
        End If

        'elenco WHERE
        sqlWhere = creaWhere()


        'costruisco FROM
        sqlFrom = " from anagrafica "
        For x = 0 To _arQuery.Count - 1
            sqlFrom += _arQuery.GetByIndex(x) & " "
        Next

        If _storico <> "2" Then
            If _tipo = 1 Then
                sqlWhere += " and Praticanti.storico=" & _storico
                If InStr(sqlFrom, "Praticanti") = 0 Then
                    sqlFrom += " INNER JOIN Praticanti ON Anagrafica.ID = Praticanti.Id_ana"
                End If
            Else
                sqlWhere += " and Alboiscritti.storico=" & _storico
                If InStr(sqlFrom, "AlboIscritti") = 0 Then
                    sqlFrom += " INNER JOIN AlboIscritti ON Anagrafica.ID = AlboIscritti.Id_ana"
                End If
            End If

        End If

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("spRisultati", getConnection)
        cmd.CommandType = CommandType.StoredProcedure

        Dim par As SqlClient.SqlParameter

        cmd.Parameters.Add("@sFrom", SqlDbType.VarChar, 1000)
        cmd.Parameters("@sFrom").Value = sqlFrom
        cmd.Parameters.Add("@sSelect", SqlDbType.VarChar, 1000)
        cmd.Parameters("@sSelect").Value = sqlSelect
        cmd.Parameters.Add("@sFiltro", SqlDbType.VarChar, 1000)
        cmd.Parameters("@sFiltro").Value = sqlWhere
        cmd.Parameters.Add("@sOrder", SqlDbType.VarChar, 500)
        cmd.Parameters("@sOrder").Value = sqlOrder


        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds, "anagrafiche")
        Dim dt As DataTable = ds.Tables("anagrafiche")

        cmd.Connection.Close()


        creaRis()
        Dim drRis As DataRow
        For Each dr In dt.Rows
            drRis = _risultato.NewRow()
            drRis("idAna") = dr("id")
            drRis("id") = "0"
            drRis("idProf") = "0"
            _risultato.Rows.Add(drRis)
        Next

        dt.Columns.RemoveAt(0)
        Return dt

    End Function

    Public Function risultatiPeriti(ByVal sId As String) As DataTable
        Dim sqlSelect As String = "select distinct anagrafica.id, "
        Dim sqlGroup As String = ""
        Dim sqlOrder As String = ""
        Dim sqlWhere As String = ""
        Dim sqlFrom As String
        Dim sql As String
        Dim x As Int16
        Dim dr As DataRow
        Dim s As String

        _arQuery.Clear()

        'elenco campi select
        Dim pos As Int16
        Dim idCampo As String
        For x = 0 To _campiVisibili.Rows.Count - 1
            dr = _campiVisibili.Rows(x)
            pos = InStr(dr("id"), "_")
            If pos > 0 Then
                idCampo = Left(dr("id"), pos - 1)
            Else
                idCampo = dr("id")
            End If
            s = datiSql(idCampo)
            If s = "" Then
                s = dr("tabella") & "." & dr("campo")
            End If

            If dr("tipo") = "3" Then
                'data
                s = "ltrim(str(day(" & s & "))) + '/' + ltrim(str(month(" & s & "))) + '/' + ltrim(str(year(" & s & "))) "
            End If
            s += " as [" & dr("etichetta") & "]"
            sqlSelect += s & ", "

        Next


        sqlSelect = Left(sqlSelect, Len(sqlSelect) - 2)

        'elenco campi da ordinare
        If _campiOrdinamento.Rows.Count > 0 Then
            sqlOrder = " order by "
        End If
        For x = 0 To _campiOrdinamento.Rows.Count - 1
            dr = _campiOrdinamento.Rows(x)
            s = datiSql(dr("id"))
            If s = "" Then
                s = dr("tabella") & "." & dr("campo")
            End If
            sqlOrder += s & ", "
        Next
        If sqlOrder <> "" Then
            sqlOrder = Left(sqlOrder, Len(sqlOrder) - 2)
        End If

        'elenco WHERE
        sqlWhere = creaWhere()


        'costruisco FROM
        sqlFrom = " from anagrafica "
        For x = 0 To _arQuery.Count - 1
            sqlFrom += _arQuery.GetByIndex(x) & " "
        Next

        If _storico <> "2" Then
            If _tipo = 1 Then
                sqlWhere += " and Praticanti.storico=" & _storico
                If InStr(sqlFrom, "Praticanti") = 0 Then
                    sqlFrom += " INNER JOIN Praticanti ON Anagrafica.ID = Praticanti.Id_ana"
                End If
            Else
                sqlWhere += " and AlboIscritti.storico=" & _storico
                If InStr(sqlFrom, "AlboIscritti") = 0 Then
                    sqlFrom += " INNER JOIN AlboIscritti ON Anagrafica.ID = AlboIscritti.Id_ana"
                End If
            End If

        End If

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("spRisultati", getConnection)
        cmd.CommandType = CommandType.StoredProcedure

        Dim par As SqlClient.SqlParameter

        cmd.Parameters.Add("@sFrom", SqlDbType.VarChar, 1000)
        cmd.Parameters("@sFrom").Value = sqlFrom
        cmd.Parameters.Add("@sSelect", SqlDbType.VarChar, 1000)
        cmd.Parameters("@sSelect").Value = sqlSelect
        cmd.Parameters.Add("@sFiltro", SqlDbType.VarChar, 1000)
        cmd.Parameters("@sFiltro").Value = sqlWhere
        cmd.Parameters.Add("@sOrder", SqlDbType.VarChar, 500)
        cmd.Parameters("@sOrder").Value = sqlOrder


        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds, "anagrafiche")
        Dim dt As DataTable = ds.Tables("anagrafiche")

        cmd.Connection.Close()


        creaRis()
        Dim drRis As DataRow
        For Each dr In dt.Rows
            drRis = _risultato.NewRow()
            drRis("id") = dr("id")
            _risultato.Rows.Add(drRis)
        Next

        dt.Columns.RemoveAt(0)
        Return dt

    End Function

    Public Function riepilogo() As String
        Dim s As String
        Dim sRiep As String = ""
        Dim x As Int16
        Dim dr As DataRow

        If Not _campiVisibili Is Nothing Then
            For x = 0 To _campiVisibili.Rows.Count - 1
                dr = _campiVisibili.Rows(x)
                s += dr("etitabella") & "." & dr("campo") & ", "
            Next
            If s <> "" Then
                sRiep = "<b>Campi visibili:</b> " & Left(s, Len(s) - 2) & "<br>"
            End If
        End If

        s = ""
        If Not _campiOrdinamento Is Nothing Then
            For x = 0 To _campiOrdinamento.Rows.Count - 1
                dr = _campiOrdinamento.Rows(x)
                s += dr("etitabella") & "." & dr("campo") & ", "
            Next
            If s <> "" Then
                sRiep += "<b>Campi ordinamento:</b> " & Left(s, Len(s) - 2) & "<br>"
            End If
        End If

        s = ""
        If Not _filtro Is Nothing Then
            For x = 0 To _filtro.Rows.Count - 1
                dr = _filtro.Rows(x)
                s += dr("etitabella") & "." & dr("campo") & ", "
            Next
            If s <> "" Then
                sRiep += "<b>Filtro:</b> " & Left(s, Len(s) - 2) & "<br>"
            End If
        End If
        Return sRiep
    End Function

    Public Function creaWhere() As String
        Dim sqlWhere As String = ""
        Dim dr As DataRow
        Dim x As Int16
        Dim s As String

        If _filtro.Rows.Count > 0 Then
            sqlWhere = " where ("
        End If
        Dim val, val2 As String

        Dim tot As Int16 = _filtro.Rows.Count - 1
        For x = 0 To tot
            If x > tot Then
                Exit For
            End If
            dr = _filtro.Rows(x)

            If dr("valore") Is System.DBNull.Value Or dr("valore2") Is System.DBNull.Value Then
                _filtro.Rows.RemoveAt(x)
                tot -= 1
                x -= 1
            Else
                s = datiSql(dr("id"))
                If s = "" Then
                    If dr("tipo") = 8 Then
                        s = dr("tabella") & ".codice"
                    Else
                        s = dr("tabella") & "." & dr("campo")
                    End If
                End If
                sqlWhere += s
                Select Case dr("tipo")
                    Case 2, 4
                        val = dr("valore")
                        val2 = dr("valore2")
                    Case 1, 6, 7, 8
                        val = "'" & Replace(dr("valore"), "'", "''") & "'"
                        val2 = "'" & Replace(dr("valore2"), "'", "''") & "'"
                    Case 3
                        val = "'" & Utility.FormattaDataxDB(dr("valore")) & "'"
                        val2 = "'" & Utility.FormattaDataxDB(dr("valore2")) & "'"
                End Select
                Select Case dr("op")
                    Case 0
                        sqlWhere += "= " & val
                    Case 1
                        sqlWhere += "< " & val
                    Case 2
                        sqlWhere += "> " & val
                    Case 3
                        sqlWhere += " like '" & dr("valore") & "%' "
                    Case 4
                        sqlWhere += " like  '%" & dr("valore") & "%' "
                    Case 5
                        sqlWhere += " between " & val & " and " & val2
                    Case 6
                        sqlWhere += " <> " & val
                End Select

                If x < _filtro.Rows.Count - 1 Then
                    sqlWhere += " " & _oper & " "
                End If
            End If
        Next

        If sqlWhere <> "" Then
            sqlWhere = sqlWhere & ") and anagrafica.albo=" & HttpContext.Current.Session("albo")
        Else
            sqlWhere += " where anagrafica.albo=" & HttpContext.Current.Session("albo")
        End If


        Return sqlWhere
    End Function
    Public Sub carica()

        _campiVisibili = getDataTable("SELECT QueryVisibili.IdCampo as id, CampiStampe.etiTabella, CampiStampe.Tabella, CampiStampe.Campo, CampiStampe.Etichetta, CampiStampe.tipo " & _
                "FROM QueryVisibili INNER JOIN   CampiStampe ON QueryVisibili.idCampo = CampiStampe.Id where queryvisibili.idQuery=" & _id)

        _campiOrdinamento = getDataTable("SELECT QueryOrd.IdCampo as id, CampiStampe.etiTabella,CampiStampe.Tabella, CampiStampe.Campo, CampiStampe.Etichetta,CampiStampe.tipo " & _
                "FROM QueryOrd INNER JOIN   CampiStampe ON QueryOrd.idCampo = CampiStampe.Id where QueryOrd.idQuery=" & _id & " order by ordine")

        _filtro = getDataTable("SELECT QueryFiltro.IdCampo as id, CampiStampe.etiTabella,CampiStampe.Tabella, CampiStampe.Campo, QueryFiltro.Op, QueryFiltro.Valore, QueryFiltro.Valore2, queryFiltro.tipo, queryFiltro.opTab, queryFiltro.valoreTab, queryFiltro.valore2Tab, queryFiltro.vista " & _
                "FROM QueryFiltro INNER JOIN CampiStampe ON QueryFiltro.IdCampo = CampiStampe.Id WHERE QueryFiltro.IdQuery = " & _id)

    End Sub

    Public Sub caricaPronta()
        _sqlPronta = getValore("select querySql from queryPronte where id = " & _id)
    End Sub


    Public Sub salva()
        Dim sql As String
        If _id = "" Then
            inserisci()
        End If
        aggiorna()
    End Sub

    Private Sub inserisci()
        _id = eseguiSPInsert("insertQuery")
    End Sub

    Private Sub aggiorna()
        Dim dr As DataRow
        Dim sql As String
        sql = "update QueryStat set nome = @nome, descrizione=@descrizione, oper =@oper, tipo = @tipo, storico = @storico where id=" & _id

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)
        cmd.Parameters.Add("@nome", _nome)
        cmd.Parameters.Add("@descrizione", _descrizione)
        cmd.Parameters.Add("@oper", _oper)
        cmd.Parameters.Add("@tipo", _tipo)
        cmd.Parameters.Add("@storico", _storico)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        'campi visibili
        eseguiSql("delete from queryVisibili where idQuery=" & _id)

        cmd.Parameters.Clear()

        sql = "insert into queryVisibili (idCampo, idQuery) values (@idCampo, @idQuery)"
        cmd.CommandText = sql
        cmd.Parameters.Add("@idCampo", 0)
        cmd.Parameters.Add("@idQuery", _id)
        cmd.Connection.Open()
        For Each dr In _campiVisibili.Rows
            cmd.Parameters("@idCampo").Value = dr("id")
            cmd.ExecuteNonQuery()
        Next
        cmd.Connection.Close()

        'campi ordinamento
        eseguiSql("delete from queryOrd where idQuery=" & _id)

        sql = "insert into queryOrd (idCampo, idQuery) values (@idCampo, @idQuery)"
        cmd.CommandText = sql
        cmd.Connection.Open()
        For Each dr In _campiOrdinamento.Rows
            cmd.Parameters("@idCampo").Value = dr("id")
            cmd.ExecuteNonQuery()
        Next
        cmd.Connection.Close()

        'campi filtro
        eseguiSql("delete from queryFiltro where idQuery=" & _id)

        sql = "insert into queryFiltro (idCampo, idQuery , op, valore, valore2, tipo, vista, opTab, valoreTab, valore2Tab) values (@idCampo, @idQuery, @op, @valore, @valore2, @tipo, @vista, @opTab, @valoreTab, @valore2Tab)"
        cmd.CommandText = sql
        cmd.Parameters.Add("@op", 0)
        cmd.Parameters.Add("@opTab", "")
        cmd.Parameters.Add("@valore", "")
        cmd.Parameters.Add("@valoreTab", "")
        cmd.Parameters.Add("@valore2Tab", "")
        cmd.Parameters.Add("@valore2", "")
        cmd.Parameters.Add("@tipo", 0)
        cmd.Parameters.Add("@vista", "")
        cmd.Connection.Open()
        For Each dr In _filtro.Rows
            cmd.Parameters("@idCampo").Value = dr("id")
            cmd.Parameters("@op").Value = dr("op")
            cmd.Parameters("@valore").Value = dr("valore")
            cmd.Parameters("@valore2").Value = dr("valore2")
            cmd.Parameters("@tipo").Value = dr("tipo")
            cmd.Parameters("@vista").Value = dr("vista")
            cmd.Parameters("@opTab").Value = dr("opTab")
            cmd.Parameters("@valoreTab").Value = dr("valoreTab")
            cmd.Parameters("@valore2Tab").Value = dr("valore2Tab")
            cmd.ExecuteNonQuery()
        Next

        cmd.Connection.Close()

    End Sub

    Public Sub elimina()
        eseguiSql("delete from queryVisibili where idQuery=" & _id)
        eseguiSql("delete from queryOrd where idQuery=" & _id)
        eseguiSql("delete from queryFiltro where idQuery=" & _id)
        eseguiSql("delete from queryStat where id=" & _id)
    End Sub
End Class

Public Class Statistiche
    Inherits Connessione

    Dim _stat As Stat

    Public Sub New(Optional ByVal newInstance As Boolean = False)

        If (HttpContext.Current.Session("STAT") Is Nothing Or newInstance) Then
            ' new instance here ;)
            _stat = New Stat
            HttpContext.Current.Session("STAT") = _stat
        Else
            _stat = HttpContext.Current.Session("STAT")
        End If
    End Sub

    Public Property Statistica() As Stat
        Get
            Return _stat
        End Get
        Set(ByVal Value As Stat)
            _stat = Value
        End Set
    End Property

    Public Function elenco(ByVal tipo As String) As DataTable
        Return getDataTable("select id, nome, descrizione, oper , storico from queryStat where tipo=" & tipo & " order by nome")
    End Function

    Public Function elencoPronte(ByVal tipo) As DataTable
        Return getDataTable("select id, nome, descrizione from queryPronte where tipo=" & tipo & " order by nome")
    End Function
End Class
